import cx_Oracle,os,sys,re,StringIO,csv,string
from xml.dom import minidom, Node
import gnosis.xml.objectify


def extractData(py_obj):
  sourceDBUser = py_obj.DB[0].dbuser.PCDATA
  sourceDBPasswd = py_obj.DB[0].dbpasswd.PCDATA
  sourceTNS = py_obj.DB[0].TNS.PCDATA
  db = cx_Oracle.connect(sourceDBUser, sourceDBPasswd, sourceTNS)
  cursor = db.cursor()
  
  i=0
  for tabel in py_obj.dTables:
    for t in tabel.dTable:
      tableName = str(tabel.dTable[i].Name.PCDATA)
      columnName = str(tabel.dTable[i].Column.PCDATA)
      conditionName = str(tabel.dTable[i].Condition.PCDATA)
      columnValue = str(tabel.dTable[i].Value.PCDATA)
      
      print tableName
      f = open(tableName+".csv", "w")
      writer = csv.writer(f, lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)
      r = cursor.execute("SELECT * FROM "+tableName+ " WHERE "+columnName+conditionName+columnValue)
      for row in cursor:
                     writer.writerow(row)
      f.close()
      i=i+1

  db.commit()
  db.close()      
  return


def extractDDL(py_obj):
  sourceDBUser = py_obj.DB[0].dbuser.PCDATA
  sourceDBPasswd = py_obj.DB[0].dbpasswd.PCDATA
  sourceTNS = py_obj.DB[0].TNS.PCDATA
  db = cx_Oracle.connect(sourceDBUser, sourceDBPasswd, sourceTNS)
  cursor = db.cursor()

  i=0
  for tabel in py_obj.dTables:
    for t in tabel.dTable:
      tableName = str(tabel.dTable[i].Name.PCDATA)
      columnName = str(tabel.dTable[i].Column.PCDATA)
      conditionName = str(tabel.dTable[i].Condition.PCDATA)
      columnValue = str(tabel.dTable[i].Value.PCDATA)
      f = open(tableName+".ddl", "w")
      cursor.execute("""BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', false); END;""")
      cursor.execute("""BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false); END;""")
      cursor.execute("""BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false); END;""")
      cursor.execute("""BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false); END;""")
      cursor.execute("""BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS', false); END;""")
      cursor.execute("""BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS', false); END;""")
      cursor.execute("""BEGIN dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false); END;""")
      db.commit()
      
      stmt1 = str("""select dbms_metadata.get_ddl('TABLE','"""+tableName+"""','"""+sourceDBUser+"""') from dual""")
      print stmt1
      writer = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC)      
      print tableName
      
      r = cursor.execute(stmt1)
      for row in cursor:
                     writer.writerow(row)
      f.close()
      #print "name:", tabel.dTable[i].Name.PCDATA
      #print "name:", tabel.dTable[i].Column.PCDATA
      #print "name:", tabel.dTable[i].Value.PCDATA
      i=i+1

  db.commit()
  db.close()

  return


def createDestinationDB(py_obj):
  DbSys = "XXXXXX"
  DbPwd = "XXXXXX"

  destDBUser = py_obj.DB[1].dbuser.PCDATA
  destDBPasswd = py_obj.DB[1].dbpasswd.PCDATA
  destTNS = py_obj.DB[1].TNS.PCDATA
 
  connection = cx_Oracle.connect(DbSys, DbPwd, DbTns)
  cursor = connection.cursor()
  cursor.arraysize = 50
  stmt0 = str('create user '+ destDBUser+' identified by '+ destDBPasswd)
  stmt1 = str('grant connect, resource, create table to '+destDBUser)
  statement = cursor.prepare(stmt0)
  cursor.execute(statement)
  statement = cursor.prepare(stmt1)
  cursor.execute(statement)
  connection.commit()
  connection.close()

  regx = '(TABLE) ("[A-Za-z0-9]*"\.)'
  replacement = "TABLE "
  

  print destDBUser, destDBPasswd, destTNS   
  connection = cx_Oracle.connect(destDBUser, destDBPasswd, destTNS)
  cursor = connection.cursor()
  cursor.arraysize = 50
  
  dir="."   
  lst = os.listdir(dir)
  filelst=[]
  for i in lst:
          ext=i.split(".")[len(i.split("."))-1]
          if ext=='ddl':
                  print i
                  reader = csv.reader(open(i, "r"))
                  lines = []
                  for line in reader:
                    stmt = str(line[0])
                    stmt = re.sub(regx, replacement, stmt)
                    print stmt
                    cursor.execute(stmt)

  connection.commit()
  connection.close()
  return

def loadData(py_obj):

  sourceDBUser = py_obj.DB[1].dbuser.PCDATA
  sourceDBPasswd = py_obj.DB[1].dbpasswd.PCDATA
  sourceTNS = py_obj.DB[1].TNS.PCDATA
  db = cx_Oracle.connect(sourceDBUser, sourceDBPasswd, sourceTNS)
  cursor = db.cursor()
  cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'")
  db.commit()
  
  dir="."
  
  lst = os.listdir(dir)
  filelst=[]
  for i in lst:
          ext=i.split(".")[len(i.split("."))-1]
          j=i.split(".")
          if ext=='csv':
                  print i, j[0]
                  reader = csv.reader(open(i, "r"))
                  lines = []
                  stmt0 = str("INSERT INTO "+j[0]+" VALUES(")
                  for index, line in enumerate(reader):
                    lines.append(line)
                    if index==0:
                      y=1
                      for i in line:
                        stmt0 =stmt0+":"+str(y)+","
                        y = y +1
                      
                  stmt0 =stmt0.rstrip(',')                      
                  stmt0 =stmt0+")" 
                  print stmt0
                  cursor.executemany(stmt0, lines)
                  db.commit()
                  
  db.close()                   
  return


class DBSubsetter:
     
     def __init__(self, name):
        self.name = name
 	xml_obj = gnosis.xml.objectify.XML_Objectify(name)
  	py_obj = xml_obj.make_instance()
	extractData(py_obj)
	extractDDL(py_obj)
	createDestinationDB(py_obj)
	loadData(py_obj)
	

class neuron ( threading.Thread ):
    def __init__ ( self, xmlFile ):
         self.xmlFile = xmlFile
         threading.Thread.__init__ ( self )

    def run ( self ):
      print self.xmlFile+ " thread"
      DBSubsetter(self.xmlFile)
     
    
if __name__ == "__main__":
    for arg in sys.argv:
        extn=arg.split(".")[len(arg.split("."))-1]
        if extn=='xml':
            neuron(arg).start()


